<?php
/*
 * This file receives (via GET) arguments that determine one psych instance.
 * It queries the db, molds the data, and writes a csv file for R processing.
 * On success, print "Success" followed by any verbose info
 * On failure, print "Error" followed by info about the error   
 */
 
 /* Examples:
 http://ec2-184-73-101-215.compute-1.amazonaws.com/psych/getData.php?shockType=Field Innovation&startDate=2011-08-10&endDate=2011-08-15&state=NH&regionType=Media Market&regionName=Burlington&measurementType=Average+Value&metric=SupportID
 http://ec2-184-73-101-215.compute-1.amazonaws.com/psych/getData.php?shockType=Field Innovation&startDate=2011-10-15&endDate=2011-10-16&state=NH&regionType=Organizer+Region&regionName=Lakes&measurementType=Progress+To+Goal&metric=SupportID
 */
 
require_once("databaseCreds.php");
require_once("connectDB.php");
require_once("getCommonFilename.php");

/* Check characters on input -- this defends against SQL injection */
/* On failure, return false */
/* Currently, few special characters allowed:
     hyphen (-) for dates and county names
     apostrophe (') for county and person names
     parens for clarification text
*/
function checkInput($value) {
  $allowed="/[A-Za-z0-9\ \-\'\(\)]+/";
  return(preg_match($allowed,$value)==1);
}

/*get correct filename*/
  global $commonFilename;
  global $dataFilename;
  global $infoFilename;
  if (preg_match("/amazon/i",$_SERVER['SERVER_NAME'])) {
    $dataFilename=$_SERVER['DOCUMENT_ROOT'] . "/psych/dataR/data" . $commonFilename . ".tab";
    $infoFilename=$_SERVER['DOCUMENT_ROOT'] . "/psych/dataR/info" . $commonFilename . ".tab";
  } else {
    $dataFilename=$_SERVER['DOCUMENT_ROOT'] . "/dataR/data.tab";    
    $infoFilename=$_SERVER['DOCUMENT_ROOT'] . "/dataR/info.tab";
  }

$paramNames=array("shockType","startDate","endDate","state","regionType",
    "regionName","measurementType","metric");

/* loop through args, validating each */
foreach ($paramNames as $paramName) {
  $arg=$_GET[$paramName];
  if(strlen($arg)==0) {
   die("Error: No content for field " . $paramName);
  }
  if(!checkInput($arg)) {
    die("Error: Illegal character in: " . $arg . " for field " . $paramName);
  }
}

deleteFiles();
$sql=getSQL();
$infoSql=getInfoSQL();

exportFromSql($sql,$infoSql);


/*
Export the data
If no rows, create a single line fake file.
*/

function exportFromSql($sql,$infoSql) {

  global $dataFilename,$infoFilename;

  $infoExport=mysql_query($infoSql) or die("Error: mysql error " . mysql_error() );
  $export=mysql_query($sql) or die("Error: mysql error " . mysql_error() );
  
  $foundRows=foundRows();
  if($foundRows>0) { 
    echo("Exported.|$foundRows|$sql");
  } else { //if no data
    deleteFiles(true,false); //only delete data file
    $noDataSql="select 0,0,0 " .
    "INTO OUTFILE '$dataFilename' FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' " .
    "from voter limit 1;";
    $export=mysql_query($noDataSql) or die("Error: mysql error " . mysql_error() );    
    echo("No data for your lookup.|$sql");
  }
}

function foundRows() {
  $res=mysql_query("Select found_rows();");
  $row = mysql_fetch_array($res, MYSQL_NUM);
  return($row[0]);
}

//syn sugar
function mres($str) {
  return(mysql_real_escape_string($str));
}

function deleteFiles($delData=true,$delInfo=true) {
  global $dataFilename,$infoFilename;

  if (file_exists($dataFilename) &&  $delData) {
    unlink($dataFilename);
  }
  if (file_exists($infoFilename) &&  $delInfo) {
    unlink($infoFilename);
  }
}

function getSQL() {

global $dataFilename;

  $startDate=$_GET["startDate"];
  $endDate=$_GET["endDate"];
  if($endDate=="" || $endDate=="NA") {
    $endDate=$startDate;
  }
 
  //determination of whether voter is in treated area
  $regionType=mres($_GET["regionType"]);
  $regionName=mres($_GET["regionName"]);
  $state=mres($_GET["state"]);
  if ($regionType=="City") { //Need better check of region type here
    $inTreatmentDetStr="if(voter.city='$regionName',1,0)";
    $regionWhereStr="region.state='$state' and region.type='None'";
  } else {
    $inTreatmentDetStr="if(region.name='$regionName',1,0)";
    $regionWhereStr="region.state='$state' and region.type='$regionType'";
  }  
 
 $inTreatmentStr= $inTreatmentDetStr . " as treated ";
  
  $measType=$_GET["measurementType"];
  $goalJoin="";
  $goalGroupBy="";
  if ($measType=="Average Value") {
    $outcomeVarStr="dataValue  as value";
  } else if ($measType=="Raw Count") {
    //TO DO: Let user specify type
    $outcomeVarStr="if(dataValue=5,1,0) as value";
  } else if ($measType=="Progress To Goal") {
    //TO DO: Let user specify type
    //TO DO: fix for multiple goals
    $outcomeVarStr="sum(if(dataValue=5,1,0)) as sm, count(dataValue) as nsize, max(DATEDIFF(goal.dateEnd,goal.dateStart)) as gdays, max(goalValue) as gValue";
    $goalJoin="inner join goal using (region_id)";
    $goalGroupBy="group by dateMeasured, $inTreatmentDetStr";
  } else {
    die("Error: Bad measurement type.");
  }
  
  $sql="select $outcomeVarStr ," .
  "if (dateMeasured<'$startDate',(-1)*DATEDIFF('$startDate',dateMeasured),if(dateMeasured>'$endDate',DATEDIFF(dateMeasured,'$endDate'),0)) as period, " .
  $inTreatmentStr .
  "INTO OUTFILE '$dataFilename' FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' " .
  " FROM ((voter inner join (voter_data inner join metric using (metric_id)) using (voter_id)) " . 
  " inner join ((region $goalJoin) inner join region_precinct using (region_id)) using (precinct_id)) " .
  " WHERE metric.name='" . mres($_GET["metric"]) . "' and " . 
  "voter.state='$state' and $regionWhereStr $goalGroupBy;";
  return($sql);
}

function getInfoSQL() {

  global $infoFilename;

  $regionType=mres($_GET["regionType"]);
  $regionName=mres($_GET["regionName"]);

  $sql="select '$regionType', '$regionName' " . 
  "INTO OUTFILE '$infoFilename' FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' " .
  " FROM voter limit 1";
  return($sql);
}


/* Replaced by outfile
//export to tab delimited
//from: http://stackoverflow.com/questions/125113/php-code-to-convert-a-mysql-query-to-csv
function save_result($export) {
  $fields = mysql_num_fields ( $export );
  
  for ( $i = 0; $i < $fields; $i++ )
  {
      $header .= mysql_field_name( $export , $i ) . "\t";
  }
  
  while( $row = mysql_fetch_row( $export ) )
  {
      $line = '';
      foreach( $row as $value )
      {                                            
          if ( ( !isset( $value ) ) || ( $value == "" ) )
          {
              $value = "\t";
          }
          else
          {
              //$value = str_replace( '"' , '""' , $value );
              //$value = '"' . $value . '"' . "\t";
              $value = $value . "\t";
          }
          $line .= $value;
      }
      $data .= trim( $line ) . "\n";
  }
  $data = str_replace( "\r" , "" , $data );
  
  if ($data=="") {
    die("Warning: no data found.");
  }
  
  echo $_SERVER['DOCUMENT_ROOT'] . "/dataR/data.tab";
  $fres=fopen($_SERVER['DOCUMENT_ROOT'] . "/dataR/data.tab","w");
  if (!$fres) {
    die("Error in opening file.");
  } else {
    fwrite($fres,$header . "\n" . $data);
    return(mysql_num_rows($export));
  }
}
 */
 
?>